import pandas as pd
import pymysql
import json


# 连接数据库

class ScoreAnalysisSystem:
    config_file = open('../config.json', 'r', encoding='utf-8')
    config = json.load(config_file)

    def __init__(self, host=config['host'], user=config['username'], passwd=config['password'], db=config['database'],
                 port=3306):
        """
        初始化数据库连接信息
        :param host: 数据库ip地址
        :param user: 用户名
        :param passwd: 密码
        :param db: 数据库名称
        :param port: 端口号，默认为3306
        """
        self.db = pymysql.connect(host=host, user=user, passwd=passwd, db=db, port=port)
        self.cursor = self.db.cursor()

    def insert_into_school(self, school_id, school_name, school_introduction):
        '''
        :param c:操作数据库的游标对象
        :param school_id:学校id
        :param school_name:学校名称
        :param school_introduction:学校简介
        :return:执行sql语句
        '''

        sql = '''insert into school (school_id, school_name, school_introduction) 
        values ('{school_id}','{school_name}','{school_introduction}') 
        '''.format(school_id=school_id, school_name=school_name, school_introduction=school_introduction)
        self.cursor.execute(sql)

    def insert_into_grade(self, grade_id, school_id, grade):
        '''
        :param c:操作数据库的游标对象
        :param grade_id:年级id
        :param school_id:学校id
        :param grade:年级
        :return:执行sql语句
        '''
        sql = '''insert into grade (grade_id, school_id, grade) 
    values ('{grade_id}','{school_id}',{grade}) 
    '''.format(grade_id=grade_id, school_id=school_id, grade=grade)
        self.cursor.execute(sql)

    def insert_into_class(self, class_id, grade_id, class_number):
        '''
        :param c:操作数据库的游标对象
        :param class_id:班级id
        :param grade_id:年级id
        :param class_number:班级号
        :return:执行sql语句
        '''
        sql = '''insert into class (class_id, grade_id, class_number) 
        values ('{class_id}','{grade_id}',{class_number})
        '''.format(class_id=class_id, grade_id=grade_id, class_number=class_number)
        self.cursor.execute(sql)

    def insert_into_student(self, student_id, name, gender, password, class_id):
        '''
        :param c:操作数据库的游标对象
        :param student_id:学生id
        :param name:学生姓名
        :param gender:学生性别
        :param password:学生密码
        :param class_id:班级id
        :return:执行sql语句
        '''
        sql = '''insert into student (student_id, name, gender, password, class_id) 
        values ('{student_id}','{name}','{gender}','{password}','{class_id}')
        '''.format(student_id=student_id, name=name, gender=gender, password=password, class_id=class_id)
        self.cursor.execute(sql)

    def insert_into_parent(self, parent_id, name, gender, phone_number, password, student_id):
        '''
        :param c:操作数据库的游标对象
        :param parent_id:家长id
        :param name:家长姓名
        :param gender:家长性别
        :param phone_number:家长电话
        :param password:家长密码
        :param student_id:学生id
        :return:执行sql语句
        '''
        sql = '''insert into parent (parent_id, name, gender, phone_number, password, student_id) 
        values ('{parent_id}','{name}','{gender}','{phone_number}','{password}','{student_id}') 
        '''.format(parent_id=parent_id, name=name, gender=gender, phone_number=phone_number, password=password,
                   student_id=student_id)
        self.cursor.execute(sql)

    def insert_into_student_parent(self, student_id, parent_id):
        '''
        :param c:操作数据库的游标对象
        :param student_id:学生id
        :param parent_id:家长id
        :return:执行sql语句
        '''
        sql = '''insert into student_parent (student_id, parent_id) 
        values ('{student_id}','{parent_id}') 
        '''.format(student_id=student_id, parent_id=parent_id)
        self.cursor.execute(sql)

    def insert_into_exam(self, exam_id, exam_name, exam_date, exam_course, exam_introduction):
        '''
        :param c:操作数据库的游标对象
        :param exam_id:考试id
        :param exam_name:考试名称
        :param exam_date:考试日期
        :param exam_course:考试科目
        :param exam_introduction:考试简介
        '''
        sql = '''insert into exam (exam_id, exam_name, exam_date, exam_course, exam_introduction)
    values ('{exam_id}','{exam_name}','{exam_date}','{exam_course}','{exam_introduction}')
    '''.format(exam_id=exam_id, exam_name=exam_name, exam_date=exam_date, exam_course=exam_course,
               exam_introduction=exam_introduction)
        self.cursor.execute(sql)

    def insert_into_score(self, score_id, student_id, exam_id, score):
        '''
        :param c:操作数据库的游标对象
        :param score_id:成绩id
        :param student_id:学生id
        :param exam_id:考试id
        :param score:成绩
        :return:执行sql语句
        '''
        sql = '''insert into score (score_id, student_id, exam_id, score)
    values ('{score_id}','{student_id}','{exam_id}',{score})
    '''.format(score_id=score_id, student_id=student_id, exam_id=exam_id, score=score)
        self.cursor.execute(sql)

    def insert_into_teacher(self, teacher_id, name, gender, phone_number, password):
        '''
        :param c:
        :param teacher_id:
        :param name:
        :param gender:
        :param phone_number:
        :param password:
        :return:
        '''
        sql = '''insert into score (teacher_id, name, gender, phone_number, password)
        values ('{teacher_id}','{name}','{gender}','{phone_number}','{password}')
        '''.format(teacher_id=teacher_id, name=name, gender=gender, phone_number=phone_number, password=password)
        self.cursor.execute(sql)

    def insert_into_teacher_class(self, teacher_id, class_id):
        '''
        :param c:
        :param teacher_id:
        :param class_id:
        :return:
        '''
        sql = '''insert into score ( teacher_id, class_id)
        values '{teacher_id}','{class_id}')
        '''.format(teacher_id=teacher_id, class_id=class_id)
        self.cursor.execute(sql)

    def delete_all_tables(self):
        sql1 = '''
            delete from student_parent
            '''
        self.cursor.execute(sql1)
        self.db.commit()

        sql2 = '''
            delete from parent
        '''
        self.cursor.execute(sql2)
        self.db.commit()

        sql3 = '''
            delete from score
                '''
        self.cursor.execute(sql3)
        self.db.commit()

        sql4 = '''
            delete from teacher_class
             '''
        self.cursor.execute(sql4)
        self.db.commit()

        sql5 = '''
            delete from teacher
                '''
        self.cursor.execute(sql5)
        self.db.commit()

        sql6 = '''
                delete from exam
                    '''
        self.cursor.execute(sql6)
        self.db.commit()

        sql7 = '''
            delete from student
        '''
        self.cursor.execute(sql7)
        self.db.commit()

        sql8 = '''
             delete from class
         '''
        self.cursor.execute(sql8)
        self.db.commit()

        sql9 = '''
            delete from grade
        '''
        self.cursor.execute(sql9)
        self.db.commit()

        sql9 = '''
               delete from school
           '''
        self.cursor.execute(sql9)
        self.db.commit()

    # 引入xlsx表格数据
    def join_data_excel(self):
        df_exam = pd.read_excel("../TestExample/exam.xlsx")
        for row in df_exam.itertuples(index=False):
            self.insert_into_exam(row.exam_id, row.exam_name, row.exam_date, row.exam_course, row.exam_introduction)

        df_school = pd.read_excel("../TestExample/school.xlsx")
        for row in df_school.itertuples(index=False):
            self.insert_into_school(row.school_id, row.school_name, row.school_introduction)

        df_grade = pd.read_excel("../TestExample/grade.xlsx")
        for row in df_grade.itertuples(index=False):
            self.insert_into_grade(row.grade_id, row.school_id, row.grade)

        df_class = pd.read_excel("../TestExample/class.xlsx")
        for row in df_class.itertuples(index=False):
            self.insert_into_class(row.class_id, row.grade_id, row.class_number)

        df_student = pd.read_excel("../TestExample/student.xlsx")
        for row in df_student.itertuples(index=False):
            self.insert_into_student(row.student_id, row.name, row.gender, row.password, row.class_id)

        df_score = pd.read_excel("../TestExample/score.xlsx")
        for row in df_score.itertuples(index=False):
            self.insert_into_score(row.score_id, row.student_id, row.exam_id, row.score)

    def join_data(self, file_name):
        df = pd.read_excel(file_name)
        for row in df.itertuples(index=False):
            self.insert_into_score(row.score_id, row.student_id, row.exam_id, row.score)

    def get_class_scores_by_exam(self, exam_ids, class_id):
        # 将 exam_id 转为字符串形式
        exam_ids_str = ','.join([f"'{exam_id}'" for exam_id in exam_ids])
        # 使用 SQL 查询语句获取考试成绩信息
        sql = f"""
            SELECT student.name, score.score, CONCAT_WS('_', exam.exam_course, RIGHT(exam.exam_id, 7)) as exam_course
            FROM student 
            INNER JOIN class ON student.class_id = class.class_id 
            INNER JOIN grade ON class.grade_id = grade.grade_id 
            INNER JOIN score ON score.student_id = student.student_id 
            INNER JOIN exam ON score.exam_id = exam.exam_id 
            WHERE exam.exam_id IN ({exam_ids_str}) AND class.class_id = '{class_id}'
        """
        self.cursor.execute(sql)
        # 将查询结果转换为 DataFrame 格式
        results = self.cursor.fetchall()
        df = pd.DataFrame(results, columns=["name", "score", "exam_course"])

        # 使用 pivot() 函数将 DataFrame 按照 exam_course 和 name 透视，并设置值为 score
        df_pivoted = df.pivot(index="name", columns="exam_course", values="score")
        df_pivoted = df_pivoted.reset_index()
        df_pivoted = df_pivoted.reset_index().rename_axis(columns=None).drop('index', axis=1)
        return df_pivoted

    def get_student_scores_by_exam_course(self, class_id, exam_course):
        # 使用 SQL 查询语句获取考试成绩信息
        sql = f"""
            SELECT student.name, score.score, CONCAT_WS('_', exam.exam_course, RIGHT(exam.exam_id, 7)) as exam_course
            FROM student 
            INNER JOIN class ON student.class_id = class.class_id 
            INNER JOIN grade ON class.grade_id = grade.grade_id 
            INNER JOIN score ON score.student_id = student.student_id 
            INNER JOIN exam ON score.exam_id = exam.exam_id 
            WHERE class.class_id = '{class_id}' AND exam.exam_course = '{exam_course}'
        """
        self.cursor.execute(sql)
        # 将查询结果转换为 DataFrame 格式
        results = self.cursor.fetchall()
        df = pd.DataFrame(results, columns=["name", "score", "exam_course"])
        df_pivoted = df.pivot(index="name", columns="exam_course", values="score")
        df_pivoted = df_pivoted.reset_index()
        df_pivoted = df_pivoted.reset_index().rename_axis(columns=None).drop('index', axis=1)
        return df_pivoted

    def get_student_scores_by_id(self, student_id):
        # 使用 SQL 查询语句获取学生的考试成绩信息
        sql = f"""
            SELECT CONCAT_WS('_', exam.exam_course, RIGHT(exam.exam_id, 7)) as exam_course, exam.exam_name, score.score, student.name as student_name
            FROM score 
            INNER JOIN exam ON score.exam_id = exam.exam_id 
            INNER JOIN student ON score.student_id = student.student_id 
            WHERE score.student_id = '{student_id}'
        """

        self.cursor.execute(sql)
        # 将查询结果转换为 DataFrame 格式
        results = self.cursor.fetchall()
        df = pd.DataFrame(results, columns=["exam_course", "exam_name", "score", "student_name"])
        # 将行索引改为“学科+考试名称”，避免重复
        df.index = df['exam_course']
        # 使用 pivot_table 函数进行透视操作
        df_pivot = pd.pivot_table(df, index='student_name', columns=df.index, values='score')
        df_pivot.index.name = None
        df_pivot.columns.name = None
        return df_pivot

    def get_class_scores_by_student(self, student_id):
        # 先查询该学生所在的班级 id
        sql = f"""
               SELECT class_id FROM student WHERE student_id = '{student_id}'
           """
        self.cursor.execute(sql)
        class_id = self.cursor.fetchall()[0][0]

        # 使用 SQL 查询语句获取班级成绩信息
        sql = f"""
               SELECT student.student_id, score.score, CONCAT_WS('_', exam.exam_course, RIGHT(exam.exam_id, 7)) AS exam_course
               FROM student 
               INNER JOIN class ON student.class_id = class.class_id 
               INNER JOIN grade ON class.grade_id = grade.grade_id 
               INNER JOIN score ON score.student_id = student.student_id 
               INNER JOIN exam ON score.exam_id = exam.exam_id 
               WHERE class.class_id = '{class_id}'
           """
        self.cursor.execute(sql)

        # 将查询结果转换为 DataFrame 格式
        results = self.cursor.fetchall()
        df = pd.DataFrame(results, columns=["student_id", "score", "exam_course"])

        # 使用 pivot() 函数将 DataFrame 按照 exam_course 和 name 透视，并设置值为 score
        df_pivoted = df.pivot(index="student_id", columns="exam_course", values="score")
        df_pivoted = df_pivoted.reset_index().rename_axis(columns=None)
        return df_pivoted

    def get_all_scores_by_class(self, class_id):
        '''
        :param c: 操作数据库的游标对象
        :param class_id: 班级id
        :return: 包含该班级所有学生所有成绩信息的DataFrame
        '''

        # 使用 SQL 查询语句获取成绩信息
        sql = f"""
            SELECT student.name, CONCAT_WS('_', exam.exam_course, RIGHT(exam.exam_id, 7)) as exam_course, score.score
            FROM student 
            INNER JOIN class ON student.class_id = class.class_id 
            INNER JOIN grade ON class.grade_id = grade.grade_id 
            INNER JOIN score ON score.student_id = student.student_id 
            INNER JOIN exam ON score.exam_id = exam.exam_id 
            WHERE class.class_id = '{class_id}'
        """
        self.cursor.execute(sql)
        # 将查询结果转换为 DataFrame 格式
        results = self.cursor.fetchall()
        df = pd.DataFrame(results, columns=["name", "exam_course", "score"])
        # 使用 pivot() 函数将 DataFrame 按照 exam_course 和 name 透视，并设置值为 score
        df_pivoted = df.pivot(index="name", columns="exam_course", values="score")
        df_pivoted = df_pivoted.reset_index()
        df_pivoted = df_pivoted.reset_index().rename_axis(columns=None).drop('index', axis=1)
        return df_pivoted
